package com.dpndeveloper.snippets.db;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import com.dpndeveloper.snippets.classloader.ClassLoaderUtil;

/**
 * @author Domenico Pio Novelli
 * 
 */
public class GenericDbUtil {

	private static ClassLoader dbClassLoader = null;

	/**
	 * <p>
	 * Creates a Sql Connection using information contained in
	 * ./config/db.properties file
	 * </p>
	 * <ul>
	 * <li>driver: Driver to usr</li>
	 * <li>url: Database url</li>
	 * <li>username: Username to access to db</li>
	 * <li>password: Password to access to db</li>
	 * </ul>
	 * Loads the driver libraries (in jar files) from the folder ./dblib
	 * 
	 * @return
	 */
	public static Connection createConnection() {

		System.out.println("Loading db properties");

		Properties properties = new Properties();
		try {
			properties.load(new FileInputStream("./config/db.properties"));
		} catch (FileNotFoundException e2) {
			System.err.println("Error connecting to the database. "
					+ "DB Configuration file not founded");
			return null;
		} catch (IOException e2) {
			e2.printStackTrace();
			System.err.println("Error connecting to the database. "
					+ "Error reading DB Configuration file");
			return null;
		}

		String driver = properties.getProperty("driver");
		String url = properties.getProperty("url");
		String username = properties.getProperty("username");
		String password = properties.getProperty("password");

		if (driver != null && url != null) {

			if (dbClassLoader == null) {
				dbClassLoader = ClassLoaderUtil.createJarClassLoader(new File(
						"./dblib"));
			}
			if (dbClassLoader != null) {

				System.out.println("Loading driver: " + driver);
				try {
					Driver driverIstance = (Driver) Class.forName(driver, true,
							dbClassLoader).newInstance();
					DriverManager.registerDriver(new MyDriver(driverIstance));
				} catch (InstantiationException e1) {
					System.err
							.println("Error connecting to the database. Error loading Driver "
									+ driver);
					e1.printStackTrace();
					return null;
				} catch (IllegalAccessException e1) {
					e1.printStackTrace();
					System.err
							.println("Error connecting to the database. Error loading Driver "
									+ driver);
					return null;
				} catch (ClassNotFoundException e1) {
					System.err
							.println("Error connecting to the database. Driver "
									+ driver + " not founded");
					return null;
				} catch (SQLException e1) {
					e1.printStackTrace();
					System.err
							.println("Error connecting to the database. Error registering Driver "
									+ driver);
				}

				try {
					System.out.println("Connecting to: " + url);
					Connection conn = DriverManager.getConnection(url,
							username, password);

					System.out.println("Connected to the database");

					return conn;

				} catch (SQLException e) {
					System.err
							.println("Error connecting to the database. Connection not created");
					e.printStackTrace();
					return null;
				}
			} else {
				System.err
						.println("Error connecting to the database. Cannot load libraries");
				return null;
			}
		} else {
			System.err
					.println("Error connecting to the database. "
							+ "DB Configuration file doesn't contain url or driver property");
			return null;
		}

	}
}
